﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[vrptEpalithefsiList]'))
DROP VIEW [vrptEpalithefsiList]
go
create view [vrptEpalithefsiList] as 
SELECT [S_N]
      ,[CLAIM_NO]
      ,[CL_NAME]
      ,[EPAL_NO]
      ,[EPAL_AMT]
      ,[EPAL_NOT_ACC]
      ,[EPAL_ACC_KAT]
      ,[EPAL_ACC_CO]
      ,[KAT_PERC_1] as KAT_PERC
      ,[CO_PERC_1] as CO_PERC
	  ,[KAT_PERC_AMT_1] as KAT_PERC_AMT
      ,[CO_PERC_AMT_1] as CO_PERC_AMT
      ,isnull([Priviledged],0) as Priviledged,1 as pmttype, DB_NAME() as CurrentDatabaseName
	  ,	isnull([KAT_PERC_AMT_1],0)+isnull([KAT_PERC_AMT_2],0)+isnull(KAT_PERC_AMT_3,0)+isnull(KAT_PERC_AMT_4,0)+isnull(KAT_PERC_AMT_5,0) +
		isnull(CO_PERC_AMT_1,0)+isnull(CO_PERC_AMT_2,0)+isnull(CO_PERC_AMT_3,0)+isnull(CO_PERC_AMT_4,0)+isnull(CO_PERC_AMT_5,0) as totalPayments
  FROM [CLAIMS]
  where isnull([KAT_PERC_1],0)>0 or isnull([CO_PERC_1],0)>0 
  union
  SELECT [S_N]
      ,[CLAIM_NO]
      ,[CL_NAME]
      ,[EPAL_NO]
      ,[EPAL_AMT]
      ,[EPAL_NOT_ACC]
      ,[EPAL_ACC_KAT]
      ,[EPAL_ACC_CO]
      ,[KAT_PERC_2] as KAT_PERC
      ,[CO_PERC_2] as CO_PERC
	  ,[KAT_PERC_AMT_2] as KAT_PERC_AMT
      ,[CO_PERC_AMT_2] as CO_PERC_AMT
      ,isnull([Priviledged],0) as Priviledged,2 as pmttype, DB_NAME() as CurrentDatabaseName
      ,	isnull([KAT_PERC_AMT_1],0)+isnull([KAT_PERC_AMT_2],0)+isnull(KAT_PERC_AMT_3,0)+isnull(KAT_PERC_AMT_4,0)+isnull(KAT_PERC_AMT_5,0) +
		isnull(CO_PERC_AMT_1,0)+isnull(CO_PERC_AMT_2,0)+isnull(CO_PERC_AMT_3,0)+isnull(CO_PERC_AMT_4,0)+isnull(CO_PERC_AMT_5,0) as totalPayments
  FROM [CLAIMS]
  where isnull([KAT_PERC_2],0)>0 or isnull([CO_PERC_2],0)>0 
  union
  SELECT [S_N]
      ,[CLAIM_NO]
      ,[CL_NAME]
      ,[EPAL_NO]
      ,[EPAL_AMT]
      ,[EPAL_NOT_ACC]
      ,[EPAL_ACC_KAT]
      ,[EPAL_ACC_CO]
      ,[KAT_PERC_3] as KAT_PERC
      ,[CO_PERC_3] as CO_PERC
	  ,[KAT_PERC_AMT_3] as KAT_PERC_AMT
      ,[CO_PERC_AMT_3] as CO_PERC_AMT
      ,isnull([Priviledged],0) as Priviledged,3 as pmttype,DB_NAME() as CurrentDatabaseName
      ,	isnull([KAT_PERC_AMT_1],0)+isnull([KAT_PERC_AMT_2],0)+isnull(KAT_PERC_AMT_3,0)+isnull(KAT_PERC_AMT_4,0)+isnull(KAT_PERC_AMT_5,0) +
		isnull(CO_PERC_AMT_1,0)+isnull(CO_PERC_AMT_2,0)+isnull(CO_PERC_AMT_3,0)+isnull(CO_PERC_AMT_4,0)+isnull(CO_PERC_AMT_5,0) as totalPayments
  FROM [CLAIMS]
  where isnull([KAT_PERC_3],0)>0 or isnull([CO_PERC_3],0)>0 
  union
  SELECT [S_N]
      ,[CLAIM_NO]
      ,[CL_NAME]
      ,[EPAL_NO]
      ,[EPAL_AMT]
      ,[EPAL_NOT_ACC]
      ,[EPAL_ACC_KAT]
      ,[EPAL_ACC_CO]
      ,[KAT_PERC_4] as KAT_PERC
      ,[CO_PERC_4] as CO_PERC
	  ,[KAT_PERC_AMT_4] as KAT_PERC_AMT
      ,[CO_PERC_AMT_4] as CO_PERC_AMT
      ,isnull([Priviledged],0) as Priviledged,4 as pmttype,DB_NAME() as CurrentDatabaseName
      ,	isnull([KAT_PERC_AMT_1],0)+isnull([KAT_PERC_AMT_2],0)+isnull(KAT_PERC_AMT_3,0)+isnull(KAT_PERC_AMT_4,0)+isnull(KAT_PERC_AMT_5,0) +
		isnull(CO_PERC_AMT_1,0)+isnull(CO_PERC_AMT_2,0)+isnull(CO_PERC_AMT_3,0)+isnull(CO_PERC_AMT_4,0)+isnull(CO_PERC_AMT_5,0) as totalPayments
  FROM [CLAIMS]
  where isnull([KAT_PERC_4],0)>0 or isnull([CO_PERC_4],0)>0
   union
  SELECT [S_N]
      ,[CLAIM_NO]
      ,[CL_NAME]
      ,[EPAL_NO]
      ,[EPAL_AMT]
      ,[EPAL_NOT_ACC]
      ,[EPAL_ACC_KAT]
      ,[EPAL_ACC_CO]
      ,[KAT_PERC_5] as KAT_PERC
      ,[CO_PERC_5] as CO_PERC
	  ,[KAT_PERC_AMT_5] as KAT_PERC_AMT
      ,[CO_PERC_AMT_5] as CO_PERC_AMT
      ,isnull([Priviledged],0) as Priviledged, 5 as pmttype,DB_NAME() as CurrentDatabaseName
      ,	isnull([KAT_PERC_AMT_1],0)+isnull([KAT_PERC_AMT_2],0)+isnull(KAT_PERC_AMT_3,0)+isnull(KAT_PERC_AMT_4,0)+isnull(KAT_PERC_AMT_5,0) +
		isnull(CO_PERC_AMT_1,0)+isnull(CO_PERC_AMT_2,0)+isnull(CO_PERC_AMT_3,0)+isnull(CO_PERC_AMT_4,0)+isnull(CO_PERC_AMT_5,0) as totalPayments
  FROM [CLAIMS]
  where isnull([KAT_PERC_5],0)>0 or isnull([CO_PERC_5],0)>0

go
delete from [Payment] where paymentid not in (select PaymentId from PaymentClaim)
go
